﻿namespace BlackHole.DAO
{
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// 访问数据库工具
    /// </summary>
    public static class Utility
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        public static readonly string ConnStr;

        /// <summary>
        /// Initializes static members of the  Utility﻿ class
        /// </summary>
        static Utility()
        {
            ConnStr = ConfigurationManager.ConnectionStrings["Larvata-MacBook"].ConnectionString;
        }

        /// <summary>
        /// 产品状态
        /// </summary>
        public enum ProductStatus
        {
            /// <summary>
            /// 全部
            /// </summary>
            全部 = 0,

            /// <summary>
            /// 在库
            /// </summary>
            在库 = 2, 

            /// <summary>
            /// 待修
            /// </summary>
            待修 = 1,
 
            /// <summary>
            /// 不在库
            /// </summary>
            不在库 = 3
        }

        #region Checked

        /// <summary>
        /// get all district from db
        /// </summary>
        /// <returns>区县列表</returns>
        public static List<District> GetAllDistricts()
        {
            List<District> ret = new List<District>();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetAllDistricts", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    District district = new District();
                    district.Id = (int)reader["Id"];
                    district.DistrictName = reader["DistrictName"].ToString();
                    ret.Add(district);
                }
            }

            return ret;
        }

        /// <summary>
        /// get all devcies from db
        /// </summary>
        /// <returns>设备列表</returns>
        public static List<Device> GetAllDevices()
        {
            List<Device> ret = new List<Device>();

            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetAllDevices", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Device device = new Device();
                    device.Id = (int)reader["Id"];
                    device.DeviceName = reader["DeviceName"].ToString();
                    ret.Add(device);
                }
            }

            return ret;
        }

        /// <summary>
        /// get all faults by device
        /// </summary>
        /// <param name="deviceId">设备Id</param>
        /// <returns>故障列表</returns>
        public static List<Fault> GetFaultsByDeviceId(int deviceId)
        {
            List<Fault> ret = new List<Fault>();

            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetFaultsByDeviceId", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@deviceId", deviceId);

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Fault fault = new Fault();
                    fault.Id = (int)reader["Id"];
                    fault.FaultName = reader["FaultName"].ToString();
                    fault.FaultCode = reader["FaultCode"].ToString();
                    ret.Add(fault);
                }
            }

            return ret;
        }

        /// <summary>
        /// get owners by districtid
        /// </summary>
        /// <param name="districtId">区县id</param>
        /// <returns>网点列表</returns>
        public static List<Owner> GetOwnersByDistrictId(int districtId)
        {
            List<Owner> ret = new List<Owner>();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetOwnersByDistrictId", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@districtId", districtId);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Owner owner = new Owner();
                    owner.Id = (int)reader["Id"];
                    owner.OwnerName = reader["OwnerName"].ToString();
                    ret.Add(owner);
                }
            }

            return ret;
        }
        #endregion

        /// <summary>
        /// 新增新增区县
        /// </summary>
        /// <param name="districtName">区县名称</param>
        /// <returns>新增成功状态</returns>
        public static bool DistrictAddNew(string districtName)
        {
            int ret;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("DistrictAddNew", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@districtName", districtName);
                conn.Open();
                ret = cmd.ExecuteNonQuery();
                return 
                    ret == 0 ? false : true;
            }
        }

        /// <summary>
        /// 新增网点
        /// </summary>
        /// <param name="districtId">区县id</param>
        /// <param name="ownerName">网点id</param>
        /// <returns>新增成功状态</returns>
        public static bool OwnerAddNew(int districtId, string ownerName)
        {
            int ret;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("OwnerAddNew", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@districtId", districtId);
                cmd.Parameters.AddWithValue("@ownerName", ownerName);
                conn.Open();
                ret = cmd.ExecuteNonQuery();
                return 
                    ret == 0 ? false : true;
            }
        }

        /// <summary>
        /// 新增设备
        /// </summary>
        /// <param name="deviceName">设备名称</param>
        /// <returns>新增成功状态</returns>
        public static bool DeviceAddNew(string deviceName)
        {
            int ret;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("DeviceAddNew", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@deviceName", deviceName);
                conn.Open();
                ret = cmd.ExecuteNonQuery();
                return 
                    ret == 0 ? false : true;
            }
        }

        /// <summary>
        /// 依据序列号获取产品
        /// </summary>
        /// <param name="serialNO">产品序列号</param>
        /// <param name="statusId">产品状态id</param>
        /// <returns>产品列表</returns>
        public static List<Product> GetProductsDetailBySerialNO(string serialNO, int statusId = 0)
        {
            List<Product> ret = new List<Product>();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetProductsDetailBySerialNO", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@serialNO", serialNO);
                cmd.Parameters.AddWithValue("@statusId", statusId);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Product product = new Product();
                    product.Id = (int)reader["Id"];
                    product.ProductDeviceId = (int)reader["DeviceId"];
                    product.ProductDeviceName = reader["DeviceName"].ToString();
                    product.ProductDistrictName = reader["DistrictName"].ToString();
                    product.ProductOwnerName = reader["OwnerName"].ToString();
                    product.StatusId = (int)reader["StatusId"];
                    product.StatusName = Enum.GetName(typeof(ProductStatus), product.StatusId);
                    product.SerialNO = reader["SerialNO"].ToString();
                    product.PsamNO = reader["PsamNO"].ToString();
                    product.RomVersion = reader["ROMVersion"].ToString();
                    product.RepairCycle = (int)reader["RepairCycle"];
                    ret.Add(product);
                }
            }

            return ret;
        }

        /// <summary>
        /// 依据网点编号获取产品数量
        /// </summary>
        /// <param name="districtId">区县ID</param>
        /// <param name="ownerId">网点id</param>
        /// <param name="deviceId">设备id</param>
        /// <param name="statusId">状态id</param>
        /// <returns>产品数量</returns>
        public static int GetProductsCountByOwnerId(int districtId, int ownerId, int deviceId = 0, int statusId = 0)
        {
            int ret;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetProductsCountByOwnerId", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@districtId", districtId);
                cmd.Parameters.AddWithValue("@ownerId", ownerId);
                cmd.Parameters.AddWithValue("@deviceId", deviceId);
                cmd.Parameters.AddWithValue("@statusId", statusId);
                conn.Open();
                ret = (int)cmd.ExecuteScalar();
            }

            return ret;
        }

        /// <summary>
        /// 产品入库 直接入库不产生记录
        /// </summary>
        /// <param name="prodcutId">产品id</param>
        /// <returns>产品出库成功状态</returns>
        public static bool ProductCheckIn(int prodcutId)
        {
            int ret;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("ProductCheckIn", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@productId", prodcutId);
                cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                conn.Open();
                cmd.ExecuteNonQuery();
                ret = (int)cmd.Parameters["@Return"].Value;
                return 
                    ret != 0 ? true : false;
            }
        }

        /// <summary>
        /// 产品维修
        /// </summary>
        /// <param name="productId">产品id</param>
        /// <param name="requestFaultId">报修故障id</param>
        /// <param name="realFaultId">实际故障id</param>
        /// <param name="repairMethod">维修方法</param>
        /// <param name="repairGear">更换备件</param>
        /// <returns>成功标志</returns>
        public static bool ProductRepair(int productId, int requestFaultId, int realFaultId, string repairMethod, string repairGear)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                int ret;
                SqlCommand cmd = new SqlCommand("ProductRepair", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@productId", productId);
                cmd.Parameters.AddWithValue("@requestFaultId", requestFaultId);
                cmd.Parameters.AddWithValue("@realFaultId", realFaultId);
                cmd.Parameters.AddWithValue("@repairMethod", repairMethod);
                cmd.Parameters.AddWithValue("@repairGear", repairGear);
                cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                conn.Open();
                cmd.ExecuteNonQuery();
                ret = (int)cmd.Parameters["@Return"].Value;
                return 
                    ret != 0 ? true : false;
            }
        }

        /// <summary>
        /// 产品出库
        /// </summary>
        /// <param name="prodcutId">产品id</param>
        /// <returns>成功标志</returns>
        public static bool ProductCheckOut(int prodcutId)
        {
            int ret;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("ProductCheckOut", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@productId", prodcutId);
                cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                conn.Open();
                cmd.ExecuteNonQuery();
                ret = (int)cmd.Parameters["@Return"].Value;
                return 
                    ret != 0 ? true : false;
            }
        }

        /// <summary>
        /// 更新产品信息
        /// </summary>
        /// <param name="productId">产品id</param>
        /// <param name="serialNO">序列号</param>
        /// <param name="psamNO">psam卡号</param>
        /// <param name="romVersion">固件版本</param>
        /// <returns>成功标志</returns>
        public static bool ProductUpdate(int productId, string serialNO, string psamNO, string romVersion)
        {
            int ret;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("ProductUpdate", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@productId", productId);
                cmd.Parameters.AddWithValue("@serialNO", serialNO);
                cmd.Parameters.AddWithValue("@psamNO", psamNO);
                cmd.Parameters.AddWithValue("@romVersion", romVersion);
                cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                conn.Open();
                cmd.ExecuteNonQuery();
                ret = (int)cmd.Parameters["@Return"].Value;
                return 
                    ret == 0 ? false : true;
            }
        }

        /// <summary>
        /// 新增产品
        /// </summary>
        /// <param name="deviceId">设备id</param>
        /// <param name="ownerId">网点id</param>
        /// <param name="serialNO">产品序列号</param>
        /// <param name="psamNO">PSAM卡号</param>
        /// <param name="romVersion">固件版本</param>
        /// <returns>成功标志</returns>
        public static bool ProductAddNew(int deviceId, int ownerId, string serialNO, string psamNO, string romVersion)
        {
            int ret;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("ProductAddNew", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@deviceId", deviceId);
                cmd.Parameters.AddWithValue("@ownerId", ownerId);
                cmd.Parameters.AddWithValue("@serialNO", serialNO);
                cmd.Parameters.AddWithValue("@psamNO", psamNO);
                cmd.Parameters.AddWithValue("@romVersion", romVersion);
                cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                conn.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                    ret = (int)cmd.Parameters["@Return"].Value;
                    return 
                        ret == 0 ? false : true;
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

        /// <summary>
        /// 新增产品并且生成维修记录
        /// </summary>
        /// <param name="deviceId">设备id</param>
        /// <param name="ownerId">网点id</param>
        /// <param name="serialNO">产品序列号</param>
        /// <param name="psamNO">pasm卡号</param>
        /// <param name="romVersion">固件版本</param>
        /// <param name="requestFaultId">报修故障id</param>
        /// <param name="realFaultId">实际故障id</param>
        /// <param name="repairMethod">维修方法</param>
        /// <param name="repairGear">更换备件</param>
        /// <returns>执行状态</returns>
        public static bool ProductAddAndRepair(int deviceId, int ownerId, string serialNO, string psamNO, string romVersion, int requestFaultId, int realFaultId, string repairMethod, string repairGear)
        {
            int ret;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("ProductAddNewAndRepair", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@deviceId", deviceId);
                cmd.Parameters.AddWithValue("@ownerId", ownerId);
                cmd.Parameters.AddWithValue("@serialNO", serialNO);
                cmd.Parameters.AddWithValue("@psamNO", psamNO);
                cmd.Parameters.AddWithValue("@romVersion", romVersion);
                cmd.Parameters.AddWithValue("@requestFaultId", requestFaultId);
                cmd.Parameters.AddWithValue("@realFaultId", realFaultId);
                cmd.Parameters.AddWithValue("@repairMethod", repairMethod);
                cmd.Parameters.AddWithValue("@repairGear", repairGear);
                cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                conn.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                    ret = (int)cmd.Parameters["@Return"].Value;
                    return
                        ret == 0 ? false : true;
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

        /// <summary>
        /// 分页方法 保留
        /// </summary>
        /// <param name="districtId">区县id</param>
        /// <param name="ownerId">网点id</param>
        /// <param name="deviceId">设备id</param>
        /// <param name="statusId">状态id</param>
        /// <param name="maximumRows">最大行</param>
        /// <param name="startRowIndex">当前行</param>
        /// <returns>产品列表</returns>
        public static List<Product> GetPagedProductsDetailByOwnerId(int districtId, int ownerId = 0, int deviceId = 0, int statusId = 0, int maximumRows = 50, int startRowIndex = 0)
        {
            List<Product> ret = new List<Product>();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetPagedProductsDetailByOwnerId", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@districtId", districtId);
                cmd.Parameters.AddWithValue("@ownerId", ownerId);
                cmd.Parameters.AddWithValue("@deviceId", deviceId);
                cmd.Parameters.AddWithValue("@statusId", statusId);
                cmd.Parameters.AddWithValue("@maximumRows", maximumRows);
                cmd.Parameters.AddWithValue("@startRowIndex", startRowIndex);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Product product = new Product();
                    product.Id = (int)reader["Id"];
                    product.ProductDeviceId = (int)reader["DeviceId"];
                    product.ProductDeviceName = reader["DeviceName"].ToString();
                    product.ProductDistrictName = reader["DistrictName"].ToString();
                    product.ProductOwnerName = reader["OwnerName"].ToString();
                    product.StatusId = (int)reader["StatusId"];
                    product.StatusName = Enum.GetName(typeof(ProductStatus), product.StatusId);
                    product.SerialNO = reader["SerialNO"].ToString();
                    product.PsamNO = reader["PsamNO"].ToString();
                    product.RomVersion = reader["ROMVersion"].ToString();
                    int tmpint = 0;
                    int.TryParse(reader["RepairCycle"].ToString(), out tmpint);
                    product.RepairCycle = tmpint;

                    ret.Add(product);
                }
            }

            return ret;
        }

        /// <summary>
        /// 检测文档是否能够被导入
        /// </summary>
        /// <param name="record">导入的记录</param>
        /// <returns>执行状态</returns>
        public static bool CheckProdcutImport(ref ImportRecord record)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("VaildateProdcutImport", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@serialNO", record.ProductSerialNO);
                cmd.Parameters.AddWithValue("@deviceId", record.ProductDeviceId);
                cmd.Parameters.AddWithValue("@ownerId", record.OwnerId);
                cmd.Parameters.AddWithValue("@psamNO", record.ProductPsamNO);
                cmd.Parameters.AddWithValue("@romVersion", record.ProductRomVersion);
                cmd.Parameters.AddWithValue("@isRecord", (record.IsRecord ? 1 : 0));
                cmd.Parameters.Add("@RET", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                conn.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                    record.ProductId = (int)cmd.Parameters["@RET"].Value;
                    bool ret =
                        ProductAddNew(record.ProductDeviceId, record.OwnerId, record.ProductSerialNO, record.ProductPsamNO, record.ProductRomVersion);
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }         
        }

        /// <summary>
        /// 获取所有产品信息 用于导出
        /// </summary>
        /// <returns>产品列表</returns>
        public static List<Product> GetAllProductsDetails()
        {
            List<Product> products = new List<Product>();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetAllProductsDetails", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Product p = new Product();
                    p.ProductDeviceName = reader["DeviceName"].ToString();
                    p.ProductDistrictName = reader["DistrictName"].ToString();
                    p.ProductOwnerName = reader["OwnerName"].ToString();
                    p.StatusName = reader["StatusName"].ToString();
                    p.SerialNO = reader["SerialNO"].ToString();
                    p.PsamNO = reader["PsamNO"].ToString();
                    p.RomVersion = reader["ROMVersion"].ToString();
                    p.RepairCycle = int.Parse("0" + reader["RepairCycle"].ToString());
                    products.Add(p);
                }
            }

            return products;
        }

        /// <summary>
        /// 获取维修记录
        /// </summary>
        /// <param name="realFaultId">实际故障id</param>
        /// <param name="districtId">区县id</param>
        /// <param name="ownerId">网点id</param>
        /// <param name="deviceId">设备id</param>
        /// <param name="inDateStart">日期筛选开始</param>
        /// <param name="outDateEnd">日期筛选结束</param>
        /// <returns>维修记录列表</returns>
        public static List<RepairRecord> GetRepairRecords(int realFaultId, int districtId, int ownerId, int deviceId, string inDateStart, string outDateEnd)
        {
            List<RepairRecord> records = new List<RepairRecord>();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                string cmdStr;
                cmdStr = "SELECT d.DistrictName,o.OwnerName,s.StatusName,p.SerialNO,p.PsamNO,p.ROMVersion,dev.DeviceName,InDate,OutDate,reaf.FaultCode+reaf.FaultName RealFaultName,r.RepairMethod,r.RepairGear ";
                cmdStr += "FROM Records r ";
                cmdStr += "JOIN Products p ON r.ProductID=p.Id ";
                cmdStr += "JOIN Owners o ON p.OwnerId=o.Id ";
                cmdStr += "JOIN Districts d ON o.DistrictId=d.Id ";
                cmdStr += "LEFT JOIN Faults reaf ON r.RealFaultID=reaf.Id ";
                cmdStr += "JOIN Statuses s ON p.StatusId=s.Id ";
                cmdStr += "JOIN Devices dev ON p.DeviceId=dev.Id ";
                cmdStr += "WHERE InDate >='" + inDateStart + "' AND InDate <='" + outDateEnd + "' AND ";

                if (realFaultId != 0)
                {
                    cmdStr += " r.RealFaultId=" + realFaultId.ToString() + " AND ";
                } 

                if (ownerId != 0)
                {
                    cmdStr += "p.OwnerId=" + ownerId.ToString() + " AND ";
                }
                else
                {
                    cmdStr += "o.DistrictId=" + districtId.ToString() + " AND ";
                }

                if (deviceId != 0)
                {
                    cmdStr += "p.DeviceId=" + deviceId.ToString() + " AND ";
                } 

                cmdStr += "1=1";
                SqlCommand cmd = new SqlCommand(cmdStr, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    RepairRecord r = new RepairRecord();
                    r.DistrictName = reader["DistrictName"].ToString();
                    r.OwnerName = reader["OwnerName"].ToString();
                    r.ProductStatus = reader["StatusName"].ToString();
                    r.ProductSerialNO = reader["SerialNO"].ToString();
                    r.ProductPsamNO = reader["PsamNo"].ToString();
                    r.ProductRomVersion = reader["RomVersion"].ToString();
                    r.ProductDeviceName = reader["DeviceName"].ToString();
                    r.RealFaultName = reader["RealFaultName"].ToString();
                    r.InDate = reader["InDate"].ToString();
                    r.OutDate = reader["OutDate"].ToString();
                    r.RepairMethod = reader["RepairMethod"].ToString();
                    r.RepairGear = reader["RepairGear"].ToString();
                    records.Add(r);
                }

                return records;
            }
        }
    }
}